package com.sl.au.repository;
import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import com.sl.au.entity.Customer;


/**
 * @author asus1
 *
 */
@Transactional(readOnly = true)
public interface CustomerRepository extends JpaRepository<Customer, String > {
	///客户信息查询，查看所有
	@Query(" select c from Customer c where c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%' ")
	Page<Customer> findCustomer(String key, Pageable pageable);
	
	//查询本人创建客户
	@Query(" select c from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and user.id=?2 ")
	Page<Customer> findCustomerByPersonId(String key,String userId,Pageable pageable);
	
	//客户信息条数
	@Query(" select count(c) from Customer c where c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%' ")
    Long getCount(String key);
		////本部门
	@Query(" select c from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and c.saleArea.superior=?2 ")
	Page<Customer> findCustomer(String key,String superior, Pageable pageable);
	
	@Query(" select count(c) from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and c.saleArea.superior=?2 ")
	Long getCount(String key,String superior);
	/////////本片区
	@Query(" select c from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and c.saleArea.superior=?2 and c.saleArea.areaName=?3 ")
	Page<Customer> findCustomer(String key,String superior,String saleArea, Pageable pageable);
	
	@Query(" select count(c) from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and c.saleArea.superior=?2 and c.saleArea.areaName=?3")
	Long getCount(String key,String superior,String saleArea);
	
	@Query(" select count(c) from Customer c where (c.customerName like '%'+?1+'%' "
			+ "or c.customerCode like '%'+?1+'%' "
			+ "or c.business like '%'+?1+'%' "
			+ "or c.saleArea.areaName like '%'+?1+'%' "
			+ "or c.buyer like '%'+?1+'%') and c.user.id = ?2")
	Long getCountByUserId(String key,String userId);
	//客户相同编号的条数
	@Query("select count(c) from Customer c where c.customerCode=?1")
	long getCodeCount(String customerCode);
	
	Customer findById(String id);
	/**根据客户编号查找
	 * @param customerCode
	 * @return
	 */
	Customer findByCustomerCode(String customerCode);
	///获得本部门的客户
	@Query("select c from Customer c where c.saleArea.superior=?1")
	List<Customer> findCustomerBySuperior(String superior);
	
	//获得自己新增的客户数
	@Query("select count(*) from Customer c where c.user.id=?1 and(c.createTime >= ?2 and c.createTime <= ?3)" )
	long getNewCount(String userId,Date start,Date end);

	@Query("select c from Customer c where c.customerName=?1 and c.saleArea.superior='销售处' order by c.updateTime desc")
	List<Customer> findByCustomerName(String name);

}
